﻿using CodeGenerator.Dal.DbHelper.Base;
using DapperDB.Dal;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeGenerator.Core
{
    public class DbTable : BaseDap
    {
        /// <summary>
        /// 获取数据库所有表名称
        /// </summary>
        public List<string> GetTableList(DBModel dbObj)
        {
            BaseDap dap = new BaseDap(dbObj);

            string _sql = "SELECT Name FROM SysObjects Where XType='U' ORDER BY Name";

            List<string> tableList = dap.Query<string>(_sql).ToList();

            return tableList;
        }
        /// <summary>
        /// 获取数据库表详细信息
        /// </summary>
        public List<TableModel> GetTableInfoList(DBModel dbObj)
        {
            BaseDap dap = new BaseDap(dbObj);

            string _sql = @"--快速查看表结构  
                                            SELECT  obj.name  tableName,     
                                                    col.name AS columnName,  
                                                    ISNULL(ep.[value], '') AS explain ,  
                                                    t.name AS dataType ,  
                                                   cast( col.length as varchar(20)) AS dataLen ,  
                                                    cast( ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0)as varchar(10)) AS digit ,   
                                                    CASE WHEN EXISTS ( SELECT   1  
                                                                       FROM     dbo.sysindexes si  
                                                                                INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id  
                                                                                                          AND si.indid = sik.indid  
                                                                                INNER JOIN dbo.syscolumns sc ON sc.id = sik.id  
                                                                                                          AND sc.colid = sik.colid  
                                                                                INNER JOIN dbo.sysobjects so ON so.name = si.name  
                                                                                                          AND so.xtype = 'PK'  
                                                                       WHERE    sc.id = col.id  
                                                                                AND sc.colid = col.colid ) THEN '√'  
                                                         ELSE ''  
                                                    END AS isPk ,  
                                                    CASE WHEN col.isnullable = 1 THEN '√'  
                                                         ELSE ''  
                                                    END AS isEmpty ,  
                                                    ISNULL(comm.text, '') AS defaultValue  
                                            FROM    dbo.syscolumns col  
                                                    LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype  
                                                    inner JOIN dbo.sysobjects obj ON col.id = obj.id  
                                                                                     AND obj.xtype = 'U'  
                                                                                     AND obj.status >= 0  
                                                    LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id  
                                                    LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id  
                                                                                                  AND col.colid = ep.minor_id  
                                                                                                  AND ep.name = 'MS_Description'  
                                                    LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id  
                                                                                                     AND epTwo.minor_id = 0  
                                                                                                     AND epTwo.name = 'MS_Description'  
                                            WHERE   obj.name in(SELECT Name FROM SysObjects Where XType='U' )
                                            ORDER BY col.colorder ;  ";

            List<TableModel> tableList = dap.Query<TableModel>(_sql).ToList();

            return tableList;
        }

        /// <summary>
        /// 判断是否为自增长
        /// </summary>  
        public bool GetPKisSelfGrowth(DBModel dbObj, string columnName)
        {
            BaseDap dap = new BaseDap(dbObj);

            string _sql = string.Format(@"select count(*)cnt from syscolumns    
                            where id = object_id('CMS_Category')
                            and name = '{0}'
                            and columnproperty(id, name, 'isidentity') = 1 ", columnName);

            int tableList = dap.Query<int>(_sql).FirstOrDefault();

            return (tableList > 0) ? true : false;
        }
    }
}
